﻿CREATE PROCEDURE [dbo].[utl_CreateSPSelBy]
@Table [sysname], @ParentObject [sysname]=NULL
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ID                   INT
	DECLARE @SQL                  NVARCHAR(MAX)
	DECLARE @ColumnSelectList     VARCHAR(MAX)
	DECLARE @ColumnUpdateList     VARCHAR(MAX)
	DECLARE @ColumnVariablesList  VARCHAR(MAX)
	DECLARE @WhereClause          VARCHAR(MAX)
	DECLARE @WhereClause2         VARCHAR(MAX)
	DECLARE @ProcedureName        sysname
	DECLARE @ColumnName           sysname
	DECLARE @Datatype             sysname
	DECLARE @QuotedColumnName     sysname
	DECLARE @VariableColumnName   sysname
	DECLARE @Schema				  sysname
	DECLARE @IsIdentity           BIT
	DECLARE @IsTimestamp          BIT
	DECLARE @IsComputed           BIT
	DECLARE @IsNullable           BIT
	DECLARE @NumericPrecision     SMALLINT
	DECLARE @NumericScale         SMALLINT
	DECLARE @MaxLength            INT
	DECLARE @IsForeignKey         BIT

	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0

	DECLARE @CRLF VARCHAR(2); SET @CRLF = CHAR(13) + CHAR(10)
	DECLARE @TAB VARCHAR(1);  SET @TAB  = CHAR(9)

	DECLARE @INDENT INT; SET @INDENT = 4

	DECLARE @Out TABLE (
		ID INTEGER IDENTITY(1,1) PRIMARY KEY,
		Line VARCHAR(MAX))
	    
	DECLARE @Columns TABLE (
		ID INTEGER IDENTITY(1,1) PRIMARY KEY,
		ColumnName sysname,
		QuotedColumnName sysname,
		VariableColumnName sysname,
		Datatype sysname,
		MaxLength INT,
		IsIdentity BIT,
		IsTimestamp BIT,
		IsComputed BIT,
		IsPartOfPrimaryKey BIT,
		IsForeignKey BIT,
		IsNullable BIT,
		NumericPrecision SMALLINT,
		NumericScale SMALLINT)

	-- make sure table exists    
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
		SET @SQL = 'Table ''%s'' does not exist.'
		RAISERROR(@SQL, 16, 1, @Table)
		RETURN 1
	END

	-- make sure table has a primary key
	IF dbo.fn_TableHasPrimaryKey(@Table) = @False
	BEGIN
		SET @SQL = 'Table ''%s'' does not have a primary key.'
		RAISERROR(@SQL, 16, 1, @Table)
		RETURN 2
	END

	-- make sure table has a timestamp column
	IF dbo.fn_TableHasTimestamp(@Table) = @False
	BEGIN
		SET @SQL = 'Table ''%s'' does not have a timestamp column.'
		RAISERROR(@SQL, 16, 1, @Table)
		RETURN 3
	END

	IF @ParentObject IS NULL
	BEGIN
		EXECUTE [dbo].[utl_CreateSPScriptTable]
		SET @ParentObject = OBJECT_NAME(@@PROCID)
	END
	
	-- build name of stored procedure to create
	SET @Schema = ISNULL(PARSENAME(@Table, 2), 'dbo')
	SET @ProcedureName = @Schema +  '.usp_' + @Table + '_SelBy'

	SET @SQL = 'DECLARE @True  BIT; SET @True  = 1'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'DECLARE @False BIT; SET @False = 0'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)

	-- force any existing procedure to be deleted
	SET @SQL = 'IF dbo.fn_ProcedureExists(''' + @ProcedureName + ''') = @True'
	INSERT INTO @Out (Line) VALUES (@SQL)
    
	SET @SQL = SPACE(@INDENT) + 'DROP PROCEDURE ' + dbo.fn_QuoteName(@ProcedureName)
	INSERT INTO @Out (Line) VALUES (@SQL)
    
	SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)
    
	SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)

	-- create a table of column names and certain attributes
	INSERT INTO @Columns
	  (
		ColumnName,
		QuotedColumnName,
		VariableColumnName,
		Datatype,
		MaxLength,
		IsIdentity,
		IsTimestamp,
		IsComputed,
		IsPartOfPrimaryKey,
		IsNullable,
		NumericPrecision,
		NumericScale,
		IsForeignKey
	  )
	SELECT ColumnName,
		   QuotedColumnName,
		   VariableColumnName,
		   Datatype,
		   MaxLength,
		   IsIdentity,
		   IsTimestamp,
		   IsComputed,
		   IsPartOfPrimaryKey,
		   IsNullable,
		   NumericPrecision,
		   NumericScale,
		   0
	FROM   dbo.fn_ColumnsAndAttributes(@Table)

	-- build proc name
	SET @SQL = 'CREATE PROCEDURE ' + dbo.fn_QuoteName(@ProcedureName) + ' ('
	INSERT INTO @Out (Line) VALUES (@SQL)

	--------------------------------------------------------------------------------
	-- build parameter list based on foreign key columns
	IF EXISTS (
		   SELECT 1
		   WHERE  (OBJECT_ID('tempdb..#sp_fkeys_results') IS NOT NULL)
	   )
		DROP TABLE #sp_fkeys_results

	CREATE TABLE #sp_fkeys_results
	(
		PKTABLE_QUALIFIER  sysname,
		PKTABLE_OWNER      sysname,
		PKTABLE_NAME       sysname,
		PKCOLUMN_NAME      sysname,
		FKTABLE_QUALIFIER  sysname,
		FKTABLE_OWNER      sysname,
		FKTABLE_NAME       sysname,
		FKCOLUMN_NAME      sysname,
		KEY_SEQ            SMALLINT,
		UPDATE_RULE        SMALLINT,
		DELETE_RULE        SMALLINT,
		FK_NAME            sysname,
		PK_NAME            sysname,
		DEFERRABILITY      SMALLINT
	)

	INSERT INTO #sp_fkeys_results
	EXECUTE sp_fkeys @fktable_name = @Table

	IF (
		   SELECT COUNT(*)
		   FROM   #sp_fkeys_results
	   ) = 0
		RETURN

	DECLARE @FKeyColumns TABLE (ID INT IDENTITY PRIMARY KEY, FKeyColumn sysname)

	INSERT INTO @FKeyColumns
	  (
		FKeyColumn
	  )
	SELECT FKCOLUMN_NAME
	FROM   #sp_fkeys_results

	IF EXISTS (
		   SELECT 1
		   WHERE  (OBJECT_ID('tempdb..#sp_fkeys_results') IS NOT NULL)
	   )
		DROP TABLE #sp_fkeys_results

	UPDATE @Columns
	SET    IsForeignKey = @True
	WHERE  EXISTS (
			   SELECT *
			   FROM   @FKeyColumns FC
			   WHERE  FC.FKeyColumn = ColumnName
		   )

	SELECT @ID = MIN(ID)
	FROM   @Columns
	WHERE  IsForeignKey = @True

	WHILE @ID IS NOT NULL
	BEGIN
		SELECT @ColumnName = ColumnName,
			   @QuotedColumnName = QuotedColumnName,
			   @VariableColumnName = VariableColumnName,
			   @Datatype = Datatype,
			   @MaxLength = MaxLength,
			   @IsIdentity = IsIdentity,
			   @IsTimestamp = IsTimestamp,
			   @IsComputed = IsComputed,
			   @NumericPrecision = NumericPrecision,
			   @NumericScale = NumericScale,
			   @IsForeignKey = IsForeignKey
		FROM   @Columns
		WHERE  ID = @ID
	    
		SET @SQL = SPACE(@INDENT) + @VariableColumnName
		SET @SQL = @SQL + ' ' + UPPER(@Datatype)
		IF @Datatype IN ('VARCHAR', 'CHAR', 'NVARCHAR', 'NCHAR')
			SET @SQL = @SQL + '(' + CAST(@MaxLength AS VARCHAR(4)) + ')'
	    
		IF @Datatype IN ('NUMERIC', 'DECIMAL')
			SET @SQL = @SQL + '(' + CAST(@NumericPrecision AS VARCHAR(2)) + ',' + 
				CAST(@NumericScale AS VARCHAR(2)) + ')'
	    
		SET @SQL = @SQL + ' = NULL'
		SELECT @ID = MIN(ID)
		FROM   @Columns
		WHERE  ID > @ID
			   AND IsForeignKey = 1
	    
		IF @ID IS NULL
			SET @SQL = @SQL + ')'
		ELSE
			SET @SQL = @SQL + ','
	    
		INSERT INTO @Out (Line) VALUES (@SQL)
	END

	--------------------------------------------------------------------------------

	-- build list of columns for select
	SELECT @ID = MIN(ID)
	FROM   @Columns

	WHILE @ID IS NOT NULL
	BEGIN
		SET @ColumnSelectList = ISNULL(@ColumnSelectList + ', ', '') + (
				SELECT QuotedColumnName
				FROM   @Columns
				WHERE  ID = @ID
			)
	    
		SELECT @ID = MIN(ID)
		FROM   @Columns
		WHERE  ID > @ID
	END

	-- tack on housekeeping columns for the refresh select
	SET @ColumnSelectList = @ColumnSelectList + dbo.fn_HousekeepingColumnList()

	-- build where clause based on foreign key columns
	SELECT @ID = MIN(ID)
	FROM   @Columns
	WHERE  IsForeignKey = @True

	WHILE @ID IS NOT NULL
	BEGIN
		SELECT @ColumnName = ColumnName,
			   @QuotedColumnName = QuotedColumnName,
			   @VariableColumnName = VariableColumnName,
			   @Datatype = Datatype,
			   @MaxLength = MaxLength,
			   @IsIdentity = IsIdentity,
			   @IsTimestamp = IsTimestamp,
			   @IsComputed = IsComputed,
			   @IsForeignKey = IsForeignKey
		FROM   @Columns
		WHERE  ID = @ID
	    
		SET @WhereClause = ISNULL(@WhereClause + ' AND ', '') + '(' + @QuotedColumnName + ' = ' + @VariableColumnName + ' OR ' + @VariableColumnName + ' IS NULL' + ')'
	    
		SELECT @ID = MIN(ID)
		FROM   @Columns
		WHERE  ID > @ID
			   AND IsForeignKey = @True
	END

	SET @SQL = 'AS'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'BEGIN'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'DECLARE @ErrorCode AS INT'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'DECLARE @RowsAffected AS INT'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'SELECT ' + @ColumnSelectList + ' FROM ' + dbo.fn_QuoteName(@Table)
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = SPACE(@INDENT) + 'WHERE ' + @WhereClause
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = SPACE(@INDENT) + 'OPTION (FAST 1)'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'RETURN @ErrorCode'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'END'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = REPLICATE('-', 80)
	INSERT INTO @Out (Line) VALUES (@SQL)

	INSERT INTO CreateSPScriptTable
	  (
		Line
	  )
	SELECT Line
	FROM   @Out
	ORDER BY
		   ID

	IF @ParentObject = OBJECT_NAME(@@PROCID)
	BEGIN
		SELECT Line
		FROM   CreateSPScriptTable
		ORDER BY
			   ID
	END
	
	SET NOCOUNT OFF

	RETURN 0

END

